package net.cl.db.sql;

import net.cl.Validator;
import net.cl.utils.StringUtil;

public class SqlBuilder {

	private StringBuilder sql;
	private boolean isWhereExist;
	private boolean isOrderByExist;
	private boolean isNextOr;

	public SqlBuilder(String sql) {
		if (sql != null) {
			if (sql.toLowerCase().contains(" where "))
				isWhereExist = true;
			if (sql.toLowerCase().contains(" order by "))
				isOrderByExist = true;
			this.sql = new StringBuilder(sql);
		} else
			this.sql = new StringBuilder();
	}

	public SqlBuilder append(Object obj) {
		return append(obj.toString());
	}

	public SqlBuilder append(String value) {
		if (Validator.isEmpty(value))
			return this;
		sql.append(" ").append(value);
		return this;
	}

	public SqlBuilder in(String columnName, Object[] values) {
		return appendIns(columnName, values, "in");
	}

	public SqlBuilder notIn(String columnName, Object[] values) {
		return appendIns(columnName, values, "not in");
	}

	public SqlBuilder or() {
		isNextOr = true;
		return this;
	}

	public SqlBuilder eq(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" = '").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder notEq(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" <> '").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder isNull(String columnName) {
		whereOrAnd();
		sql.append(columnName);
		sql.append(" is null");
		return this;
	}

	public SqlBuilder notNull(String columnName) {
		whereOrAnd();
		sql.append(columnName);
		sql.append(" is not null");
		return this;
	}

	public SqlBuilder ge(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" >= '").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder gt(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" > '").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder le(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" <= '").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder lt(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" < '").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder orderBy(String order) {
		if (Validator.isEmpty(order))
			return this;
		if (isOrderByExist)
			return this;
		sql.append(" order by ");
		sql.append(order);
		isOrderByExist = true;
		return this;
	}

	public SqlBuilder like(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" like '%").append(escapeSql(value.toString())).append("%'");
		return this;
	}

	public SqlBuilder leftLike(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" like '%").append(escapeSql(value.toString())).append("'");
		return this;
	}

	public SqlBuilder rightLike(String columnName, Object value) {
		if (isEmpty(value))
			return this;
		whereOrAnd();
		sql.append(columnName);
		sql.append(" like '").append(escapeSql(value.toString())).append("%'");
		return this;
	}

	public SqlBuilder bracket() {
		sql.append(" ( ");
		return this;
	}

	public SqlBuilder closeBracket() {
		sql.append(" ) ");
		return this;
	}

	public String getSql() {
		return toString();
	}

	public String toString() {
		return sql.toString();
	}

	private void whereOrAnd() {
		if (!isWhereExist) {
			sql.append(" where ");
			isWhereExist = true;
		} else {
			if (isNextOr) {
				sql.append(" or ");
				isNextOr = false;
			} else
				sql.append(" and ");
		}
	}

	private SqlBuilder appendIns(String columnName, Object[] values, String sin) {
		if (values != null && values.length > 0) {
			whereOrAnd();
			String s = null;
			sql.append(columnName).append(" ").append(sin).append(" (");
			for (int i = 0; i < values.length; i++) {
				s = values[i] == null ? "" : values[i].toString();
				sql.append("'").append(escapeSql(s)).append("'");
				if (i < values.length - 1)
					sql.append(",");
			}
			sql.append(")");
		}
		return this;
	}

	private String escapeSql(String s) {
		return s.replace("'", "''");
	}

	private boolean isEmpty(Object o) {
		return Validator.isEmpty(StringUtil.nullValue(o));
	}

}
